############################## Specific ##############################
#
#
#
#
#
#
#################################################################

############################# Add moldings ############################
proc add_molding {table} {
	set data [dict create]
	set keys [dict keys $::form::fdata]
	set values [dict values $::form::fdata]
	for {set i 0} {$i < [llength $keys]} {incr i} {
		dict set data [lindex $keys $i] [[lindex $values $i] get]	
	}
# 	puts $data
	set data_moldings [dict remove $data places direction1 direction2]
	$::db insert_all $table $data_moldings
	set a [$::db select_last_inserted $table id]
	if {[dict get $data sides] == 1} {
			set query "INSERT INTO sides (fk_moldings, places, direction1) VALUES ('$a' , '[dict get $data places]' , '[dict get $data direction1]')"
			$::db execute_query $query
			for {set e 0} {$e< [dict get $data places]} {incr e} {
				set query "INSERT INTO places (fk_sides,fk_moldings) VALUES ('[$::db select_last_inserted sides id]','$a')"
				$::db execute_query $query
				set query "INSERT INTO current_status (fk_places, fk_status) VALUES ('[$::db select_last_inserted places id]','1')"
				$::db execute_query $query
			}
	} elseif {[dict get $data sides] == 2} {
		for {set i 1} {$i <= 2} {incr i} {
			set query "INSERT INTO sides (fk_moldings, places, direction$i) VALUES ('$a' , '[dict get $data places]' , '[dict get $data direction$i]')"
			$::db execute_query $query
			for {set e 0} {$e< [dict get $data places]} {incr e} {
				set query "INSERT INTO places (fk_sides,fk_moldings) VALUES ('[$::db select_last_inserted sides id]','$a')"
				$::db execute_query $query
				set query "INSERT INTO current_status (fk_places, fk_status) VALUES ('[$::db select_last_inserted places id]','1')"
				$::db execute_query $query
			}

		}
	}
}

proc update_mold {table} {
	set data [dict create]
	set places [[dict get $::form::fdata places] get]
	set new_data [dict remove $::form::fdata places]
	foreach key [dict keys $new_data] value [dict values $new_data] {
		if {[string match fk_* $key] == 1} {
			set fktable [string trim $key fk_]
			dict set data $key [$::db select_id_by_name $fktable [$value get]]
		} else {
			dict set data $key [$value get]
		}
	}
	set query "DELETE FROM places WHERE fk_moldings='[dict get $data id]'"
	set query2 "DELETE FROM sides WHERE fk_moldings='[dict get $data id]'"
	# puts $data
	$::db execute_query $query
	$::db execute_query $query2

	set a [dict get $data id]
	dict append data places $places
	if {[dict get $data sides] == 1} {
			set query "INSERT INTO sides (fk_moldings, places, direction1) VALUES ('$a' , '[dict get $data places]' , '[dict get $data direction1]')"
			$::db execute_query $query
			for {set e 0} {$e< [dict get $data places]} {incr e} {
				set query "INSERT INTO places (fk_sides,fk_moldings) VALUES ('[$::db select_last_inserted sides id]','$a')"
				$::db execute_query $query
				set query "INSERT INTO current_status (fk_places, fk_status) VALUES ('[$::db select_last_inserted places id]','1')"
				$::db execute_query $query
			}
	} elseif {[dict get $data sides] == 2} {
		for {set i 1} {$i <= 2} {incr i} {
			set query "INSERT INTO sides (fk_moldings, places, direction$i) VALUES ('$a' , '[dict get $data places]' , '[dict get $data direction$i]')"
			$::db execute_query $query
			for {set e 0} {$e< [dict get $data places]} {incr e} {
				set query "INSERT INTO places (fk_sides,fk_moldings) VALUES ('[$::db select_last_inserted sides id]','$a')"
				$::db execute_query $query
				set query "INSERT INTO current_status (fk_places, fk_status) VALUES ('[$::db select_last_inserted places id]','1')"
				$::db execute_query $query
			}
		}
	}
	set data_moldings [dict remove $data direction1 direction2]
	$::db update_all $table $data_moldings
}


############################ Delete moldings ############################


proc edit_client {tablelist} {
	set ::form::sdata [dict create]
	set client_var {id name contact phone fax cell email web}
	set row [$tablelist rowcget [$tablelist curselection] -text]
	foreach key $client_var value $row {
		dict set ::form::sdata $key $value
	}
	load_main client client_edit.xml $::main
}

proc edit_moldings {tablelist} {
	set ::form::sdata [dict create]
	set moldings_var {id fk_type fk_island fk_district fk_zone fk_owner sides direction1 direction2}
	set row [$tablelist rowcget [$tablelist curselection] -text]
	foreach key $moldings_var value $row {
		dict set ::form::sdata $key $value
	}
	load_main moldings moldings_edit.xml $::main
}

############################## Busy ##############################
proc busy_row {tablelist} {
	set row [$tablelist rowcget [$tablelist curselection] -text]
	set ::form::sdata [dict create fk_current [$tablelist cellcget [$tablelist curselection],0 -text] \
		fk_places [$tablelist cellcget [$tablelist curselection],1 -text] fk_sides [$tablelist cellcget [$tablelist curselection],2 -text] \
		fk_moldings [$tablelist cellcget [$tablelist curselection],3 -text] fk_type [$tablelist cellcget [$tablelist curselection],5 -text] \
		fk_island [$tablelist cellcget [$tablelist curselection],6 -text] fk_district [$tablelist cellcget [$tablelist curselection],7 -text]  \
		fk_zone [$tablelist cellcget [$tablelist curselection],8 -text], price ""]
	if {[check_state [lindex $row 0] busy] != 0} {
		load_main current_busy current_busy.xml $::main}
}

proc reserve_row {tablelist} {
	set row [$tablelist rowcget [$tablelist curselection] -text]
	set ::form::sdata [dict create fk_current [$tablelist cellcget [$tablelist curselection],0 -text] \
		fk_places [$tablelist cellcget [$tablelist curselection],1 -text] fk_sides [$tablelist cellcget [$tablelist curselection],2 -text] \
		fk_moldings [$tablelist cellcget [$tablelist curselection],3 -text] fk_type [$tablelist cellcget [$tablelist curselection],5 -text] \
		fk_island [$tablelist cellcget [$tablelist curselection],6 -text] fk_district [$tablelist cellcget [$tablelist curselection],7 -text]  \
		fk_zone [$tablelist cellcget [$tablelist curselection],8 -text], price ""]
	if {[check_state [lindex $row 0] reserve] != 0} {
		load_main current_reserved current_reserved.xml $::main}
}

proc confirm_reserved {tablelist} {	
	set row [$tablelist rowcget [$tablelist curselection] -text]
	set query1 "SELECT reserved FROM current_status WHERE id=[lindex $row 0]"
	set get_reserved_id [$::db execute_query $query1]
	set query2 "INSERT INTO current_busy (fk_places, fk_client, init_date, end_date, fk_reason, price) SELECT fk_places, fk_client, init_date, end_date, fk_reason, price FROM reserved WHERE id='$get_reserved_id'"
	$::db execute_query $query2
	set a [$::db select_last_inserted current_busy id]
	set query3 "UPDATE current_status SET fk_status='2', busy='$a', reserved=NULL WHERE id='[lindex $row 0]'"
	$::db execute_query $query3
	set query4 "DELETE FROM reserved WHERE id='$get_reserved_id'"
	$::db execute_query $query4
	general_reserved_expire
}

proc cancel_reserved {tablelist} {	
	set row [$tablelist rowcget [$tablelist curselection] -text]
	if {[set n_stat [check_state [lindex $row 0] cancel_reserve]] != 0} {
		set query1 "SELECT reserved FROM current_status WHERE id=[lindex $row 0]"
		set get_reserved_id [$::db execute_query $query1]
		set query2 "DELETE FROM reserved WHERE id='$get_reserved_id'"
		$::db execute_query $query2

		set query3 "UPDATE current_status SET fk_status='$n_stat', reserved=NULL WHERE id='[lindex $row 0]'"
		$::db execute_query $query3
		general_reserved_expire
	}
}

proc make_busy {} {
	set data [dict create]
	set keys [dict keys $::form::fdata]
	set values [dict values $::form::fdata]
	for {set i 0} {$i < [llength $keys]} {incr i} {
		if {[string match -nocase *.e [lindex $values $i]] == 1} {
			dict set data [lindex $keys $i] [[lindex $values $i] get]	
		} elseif {[string match -nocase *.ebin [lindex $values $i]] == 1} {
			if {[[lindex $values $i] get] == ""} {
			} else {
				set fbin [open "[[lindex $values $i] get]" "r"]
				fconfigure $fbin -translation binary
				set content [read $fbin]
				dict set data [lindex $keys $i] $content
				close $fbin
			}
		} else {
			dict set data [lindex $keys $i] [[lindex $values $i] get]	
		}
	}

	set n_stat [check_state [dict get $data fk_current] busy]
	set query "INSERT INTO current_busy (fk_places, fk_client, init_date, end_date, fk_reason, price) \
	VALUES ('[dict get $data fk_places]',(SELECT id FROM client WHERE name='[dict get $data fk_client]'), '[dict get $data init_date]','[dict get $data end_date]', \
	(SELECT id FROM reason WHERE name='[dict get $data fk_reason]'),'[dict get $data price]')"
	$::db execute_query $query
	set query2 "UPDATE current_status SET fk_status='$n_stat', busy='[$::db select_last_inserted current_busy id]' WHERE id='[dict get $data fk_current]'"
	$::db execute_query $query2
}

############################# Reserved #############################
proc make_reserved {} {
	set data [dict create]
	set keys [dict keys $::form::fdata]
	set values [dict values $::form::fdata]
	for {set i 0} {$i < [llength $keys]} {incr i} {
		if {[string match -nocase *.e [lindex $values $i]] == 1} {
			dict set data [lindex $keys $i] [[lindex $values $i] get]	
		} elseif {[string match -nocase *.ebin [lindex $values $i]] == 1} {
			if {[[lindex $values $i] get] == ""} {
			} else {
				set fbin [open "[[lindex $values $i] get]" "r"]
				fconfigure $fbin -translation binary
				set content [read $fbin]
				dict set data [lindex $keys $i] $content
				close $fbin
			}
		} else {
			dict set data [lindex $keys $i] [[lindex $values $i] get]	
		}
	}
	set n_stat [check_state [dict get $data fk_current] reserve]
	set query "INSERT INTO reserved (fk_places, fk_client, init_date, end_date, fk_reason, price) \
	VALUES ('[dict get $data fk_places]',(SELECT id FROM client WHERE name='[dict get $data fk_client]'), '[dict get $data init_date]','[dict get $data end_date]', \
	(SELECT id FROM reason WHERE name='[dict get $data fk_reason]'), '[dict get $data price]')"
	$::db execute_query $query
	set query3 "UPDATE current_status SET fk_status='$n_stat', reserved='[$::db select_last_inserted reserved id]' WHERE id='[dict get $data fk_current]'"
	$::db execute_query $query3
}


proc make_free {tablelist} {
	set row [$tablelist rowcget [$tablelist curselection] -text]
	check_op $row
	if {[set n_stat [check_state [lindex $row 0] free]] != 0} {
		set query "UPDATE current_status SET fk_status='$n_stat', busy=NULL WHERE id=[lindex $row 0]"
		$::db execute_query $query
		general_busy_expire
	}
}

proc check_op {row} {
	set query "SELECT fk_po FROM current_status"
}

proc check_state {id action} {
	set query1 "SELECT fk_status FROM current_status WHERE id='$id'"
	set c_stat [$::db execute_query $query1]
	if {$action == "busy"} {
		if {$c_stat == 1} {
			set n_stat 2
		} elseif {$c_stat == 3} {
			set n_stat 4
		} else {
			tk_messageBox -message  [::msgcat::mc "Space already busy"]
			set n_stat 0
			}
	}
	if {$action == "reserve"} {
		if {$c_stat == 1} {
			set n_stat 3
		} elseif {$c_stat == 2} {
			set n_stat 4
		} else {
			tk_messageBox -message [::msgcat::mc "Space already reserved"]
			set n_stat 0
			}
	}
	if {$action == "free"} {
		if {$c_stat == 2} {
			set n_stat 1
		} elseif {$c_stat == 4} {
			set n_stat 3
		} else {
			tk_messageBox -message [::msgcat::mc "Space already empty"]
			set n_stat 0
			}
	}
	if {$action == "cancel_reserve"} {
		if {$c_stat == 3} {
			set n_stat 1
		} elseif {$c_stat == 4} {
			set n_stat 2
		} else {
			tk_messageBox -message [::msgcat::mc "There is no reserve for this space"]
			set n_stat 0
			}
	}
	return $n_stat
}

############################## Info ##############################
proc moldings_info {tablelist} {
	set row [$tablelist rowcget [$tablelist curselection] -text]
	set ::form::sdata [dict create]

	set d_vars {fk_current fk_places fk_sides fk_moldings fk_status fk_type fk_island fk_district fk_zone direction1 direction2 fk_client init_date end_date fk_reason hist}

	if {[lindex $row 4] == "Ocupado" || [lindex $row 4] == "Ocupado / Reservado"} {
		set query "SELECT fk_client, init_date, end_date, fk_reason FROM current_busy, current_status WHERE current_status.busy = current_busy.id AND current_status.fk_places = '[lindex $row 1]'"
		set client_v [lindex [$::db execute_query $query] 0]
		set query2 "SELECT name FROM client WHERE id='[lindex $client_v 0]'"
		set query3 "SELECT name FROM reason WHERE id='[lindex $client_v 3]'"
		set client_name [lindex [lindex [$::db execute_query $query2] 0] 0]
		set reason_name [lindex [lindex [$::db execute_query $query3] 0] 0]
		set client_v [lreplace $client_v 0 0 $client_name]
		set client_v [lreplace $client_v 3 3 $reason_name]
		set n_row [concat $row $client_v]
	} elseif {[lindex $row 4] == "Vazio / Reservado" || [lindex $row 4] == "Ocupado / Reservado"} {
		set query "SELECT fk_client, init_date, end_date, fk_reason FROM reserved, current_status WHERE current_status.reserved = reserved.id AND current_status.fk_places = '[lindex $row 1]'"
		set client_v [lindex [$::db execute_query $query] 0]
		set query2 "SELECT name FROM client WHERE id='[lindex $client_v 0]'"
		set query3 "SELECT name FROM reason WHERE id='[lindex $client_v 3]'"
		set client_name [lindex [lindex [$::db execute_query $query2] 0] 0]
		set reason_name [lindex [lindex [$::db execute_query $query3] 0] 0]
		set client_v [lreplace $client_v 0 0 $client_name]
		set client_v [lreplace $client_v 3 3 $reason_name]
		set n_row [concat $row $client_v]
	} else {set n_row $row}

	foreach val $n_row  var $d_vars {
		dict set ::form::sdata $var $val
	}
	set query "SELECT fk_client, init_date, end_date, fk_reason, price FROM current_busy WHERE current_busy.fk_places = '[lindex $row 1]'"

	set v_hist [$::db execute_query $query]
	set y_list {}
	for {set i 0} {$i < [llength $v_hist]} {incr i} {
		set a [lindex $v_hist $i]
		set query "SELECT name FROM client WHERE id='[lindex $a 0]'"
		set query2 "SELECT name FROM reason WHERE id='[lindex $a 3]'"
		set v_hist_cl [lindex [lindex  [$::db execute_query $query] 0] 0]
		set v_hist_r [lindex [lindex [$::db execute_query $query2] 0] 0]
		set tmp_list {}
		lappend tmp_list $v_hist_cl [lindex $a 1] [lindex $a 2] $v_hist_r [lindex $a 4]
		lappend y_list $tmp_list
	}
	set v_hist [$::db llist_to_dict $y_list {fk_client, init_date, end_date, fk_reason}]

	dict set ::form::sdata hist $v_hist
	load_main moldings_info moldings_info.xml $::main
}

proc general_busy_expire {} {
	set ::form::sdata [dict create]
	set systemTime [clock seconds]
	set plus_five [clock format [clock scan {+5 days} -base $systemTime] -format {%Y-%m-%d}]
	set minus_five [clock format [clock scan {-5 days} -base $systemTime] -format {%Y-%m-%d}]

	set o [$::db select_all v_current_busy end_date $minus_five $plus_five]

	dict set ::form::sdata today [clock format $systemTime -format {%Y-%m-%d}]
	dict set ::form::sdata expire_busy $o
	load_main general general_busy_expire.xml $::main
}

proc general_reserved_expire {} {
	set ::form::sdata [dict create]
	set systemTime [clock seconds]
	set plus_five [clock format [clock scan {+5 days} -base $systemTime] -format {%Y-%m-%d}]
	set minus_five [clock format [clock scan {-5 days} -base $systemTime] -format {%Y-%m-%d}]
# 	set o [$::db select_all_between_dates reserved init_date $minus_five $plus_five]
	set o [$::db select_all v_current_reserved init_date $minus_five $plus_five]
	dict set ::form::sdata today [clock format $systemTime -format {%Y-%m-%d}]
	dict set ::form::sdata expire_reserved $o
	load_main general general_reserved_expire.xml $::main
}


proc filter_table {table tablelist} {
# puts $tablelist
	set search_w .searchw
	catch {destroy $search_w}
	toplevel $search_w
	wm title $search_w [::msgcat::mc "Filter Result"]
	set search_main [frame $search_w.search_main -bg red -bd 3 -width 200]
	pack $search_main -fill both
	if {[info exists ::form::sdata] == 1} {unset ::form::sdata}
	set columns_list {}
	set count_columns [$tablelist columnindex end]

	set columns [$::db select_columns_names $table]

	set count_columns [$tablelist columnindex end]
	for {set i 0} {$i<=$count_columns} {incr i} {
		lappend columns_list [$tablelist columncget $i -title]
	}
	set xmlfile [new_file search_xml.xml]
	puts $xmlfile "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
	puts $xmlfile "<form>"
	puts $xmlfile "<container type=\"frame\" pos=\"hor\" expand=\"yes\">"

	foreach col $columns col_name $columns_list {
		puts $xmlfile "<entry lsize=\"15\" esize=\"50\" var=\"$col\">$col_name</entry>"
	}

	puts $xmlfile "<line></line>"
	puts $xmlfile "<button cmd=\"make_search $table\">OK</button>"
	puts $xmlfile "</container>"
	puts $xmlfile "</form>"
	close $xmlfile
	load_main tmp search_xml.xml $search_main
}

# proc make_search {table} {
	# set data [dict create]
	# foreach key [dict keys $::form::fdata] value [dict values $::form::fdata] {
		# dict set data $key [$value get]
	# }
	# set data [dict create]
	# foreach key [dict keys $::form::fdata] value [dict values $::form::fdata] {
		# dict set data $key [$value get]
	# }
	# set ::form::sdata [dict create]	

	# switch $table {
		# client {
			# dict set ::form::sdata client [$::db select_all_case $table $data]
			# load_main client client.xml $::main
		# }
		# v_current_status {
			# dict set ::form::sdata v_current_status [$::db select_all_case $table $data]
			# load_main current_status current_status.xml $::main
		# } 
		# type {
			# dict set ::form::sdata type [$::db select_all_case $table $data]
			# load_main type type.xml $::main
		# } 
		# moldings {
			# dict set ::form::sdata moldings [$::db select_all_case $table $data]
			# load_main moldings moldings.xml $::main
		# }
		# default {puts "some other value"}
	# }

	# destroy .searchw
# }

proc moldings_by_client {tablelist} {
	set row [$tablelist rowcget [$tablelist curselection] -text]

	set ::form::sdata [dict create]
	set headers {id places_id sides_id moldings_id fk_status fk_type fk_client init_date end_date fk_reason island_name district_name zone_name direction1 direction2}
# puts [lindex $row 0]
	set query "SELECT * FROM v_current_busy WHERE fk_client='[lindex $row 0]'"
	set mol_by_user [$::db execute_query $query]

	set count_line [llength $mol_by_user]

	set rows [dict create]

	for {set i 0} {$i < $count_line} {incr i} {
		set data [dict create]
		set line [lindex $mol_by_user $i]
		foreach val $line header $headers {
			if {[string match fk_* $header] == 1} {
				set fktable [string trim $header fk_]
				set query "SELECT name FROM $fktable WHERE id=$val"
				set val [lindex [lindex [$::db execute_query $query] 0] 0]
				dict set data $header $val
			} else { dict set data $header $val }
		}
		dict set rows $i $data
	}
	dict set ::form::sdata place $rows
	load_main client client_place.xml $::main
}


# proc select_outdoor {} {	
# 	set ::form::sdata [dict create]
# 
# # 	set query "SELECT * FROM moldings WHERE fk_type=1 OR fk_type=2 OR fk_type=3 OR fk_type=4"
# # 	set llist [$::db execute_query $query]
# 	set cas "OR fk_type=1 OR fk_type=2 OR fk_type=3 OR fk_type=4"
# 	set result [$::db select_all2_case moldings * $cas]
# 
# 	set columnslist {fk_type fk_owner fk_island fk_district fk_zone sides places direction1 direction2}
# 
# # 	set res [$::db llist_to_dict $llist $columnslist]
# 
# 	dict set ::form::sdata moldings $result
# 	load_main moldings moldings.xml $::main
# 
# }
# proc select_mupie {} {	
# 	set ::form::sdata [dict create]
# 
# # 	set query "SELECT * FROM moldings WHERE fk_type=1 OR fk_type=2 OR fk_type=3 OR fk_type=4"
# # 	set llist [$::db execute_query $query]
# 	set cas "OR fk_type=1 OR fk_type=2 OR fk_type=3 OR fk_type=4"
# 	set result [$::db select_all2_case moldings * $cas]
# 
# 	set columnslist {fk_type fk_owner fk_island fk_district fk_zone sides places direction1 direction2}
# 
# # 	set res [$::db llist_to_dict $llist $columnslist]
# 
# 	dict set ::form::sdata moldings $result
# 	load_main moldings moldings.xml $::main
# 
# }

proc add_moldings_to_op {tablelist} {
# 	if {[info exists ::form::sdata] == 1} {unset ::form::sdata}
	set ::form::sdata [dict create]
	set po_id [$tablelist cellcget [$tablelist curselection],0 -text]
	dict set ::form::sdata fk_po $po_id

	load_main po add_moldings_to_po.xml $::main
}

proc confirm_moldings_to_op {tablelist} {
	set data [dict create]
	foreach key [dict keys $::form::fdata] value [dict values $::form::fdata] {
		dict set data $key [$value get] 
	}
	set query "SELECT * FROM po WHERE id='[dict get $data fk_po]'"
	set data_from_po [lindex [$::db execute_query $query] 0]
	set rows [$tablelist curselection]
	foreach val $rows {
		set space_id [$tablelist cellcget  $val,1 -text]
		set query "INSERT INTO current_busy( fk_places, fk_client, init_date, end_date, fk_reason, fk_po, price) VALUES ('$space_id', '[lindex $data_from_po 2]' , '[lindex $data_from_po 4]' , '[lindex $data_from_po 5]', '[lindex $data_from_po 3]' , '[dict get $data fk_po]', '[dict get $data price]')"
		$::db execute_query $query
		set a [$::db select_last_inserted current_busy id]
		set query "UPDATE current_status SET fk_status='2', busy='$a', reserved=NULL WHERE fk_places='$space_id'"
		$::db execute_query $query
	}
}

proc view_po_moldings {tablelist} {
	set ::form::sdata [dict create]
	set po_name [$tablelist cellcget [$tablelist curselection],1 -text]
	set query "SELECT * FROM all_op WHERE po_name='$po_name'"
	set s_data [$::db execute_query $query]
	set columnslist [$::db select_columns_names all_op]
	dict set ::form::sdata molds_by_po [$::db llist_to_dict $s_data $columnslist]

	set m_subtotal [$::db execute_query "SELECT SUM(price) FROM all_op WHERE po_name='$po_name'"]
	dict set ::form::sdata m_subtotal $m_subtotal

	set query "SELECT (end_date - init_date) FROM po WHERE name='$po_name'"
	set days [lindex [$::db execute_query $query] 0]
	dict set ::form::sdata op_total [expr {[months $days] * $m_subtotal}]

	load_main po moldings_by_po.xml $::main
}





proc reports {} {
	set ::form::sdata [dict create]
	set total_spaces [$::db execute_query "SELECT COUNT(*) FROM current_status"]
	dict set ::form::sdata total_spaces $total_spaces

	set total_ocup [$::db execute_query "SELECT COUNT(*) FROM v_current_busy"]
	dict set ::form::sdata total_ocup $total_ocup
	set total_ocup_pr [expr { double($total_ocup) / (double($total_spaces) / 100) }]
	dict set ::form::sdata total_ocup_pr $total_ocup_pr

	set total_free [$::db execute_query "SELECT COUNT(*) FROM v_current_empty"]
	dict set ::form::sdata total_free $total_free
	set total_free_pr [expr { double($total_free) / (double($total_spaces) / 100) }]
	dict set ::form::sdata total_free_pr $total_free_pr

	set total_resrv [$::db execute_query "SELECT COUNT(*) FROM v_current_reserved"]
	dict set ::form::sdata total_resrv $total_resrv
	set total_resrv_pr [expr { double($total_resrv) / (double($total_spaces) / 100) }]
	dict set ::form::sdata total_resrv_pr $total_resrv_pr
	dict set ::form::sdata year [clock format [clock seconds] -format {%Y}]
	load_main report report_main.xml $::main
}

proc get_5years {} {
	set curr_year [clock format [clock seconds] -format {%Y}]
	set result "[expr {$curr_year - 2}] [expr {$curr_year - 1}] $curr_year [expr {$curr_year + 1}] [expr {$curr_year + 2}]"
}

proc fin_preview_busy {} {
	set data [dict create]
	set keys [dict keys $::form::fdata]
	set values [dict values $::form::fdata]
	for {set i 0} {$i < [llength $keys]} {incr i} {
		dict set data [lindex $keys $i] [[lindex $values $i] get]	
	}

	set curr_year [dict get $data year]
	set month [dict get $data month]
	if {$month != ""} {
		switch $month {
			1 {
				set start "$curr_year-01-01"
				set end "$curr_year-01-31"
			}
			2 {
				set start "$curr_year-02-01"
				set end "$curr_year-02-28"
			}
			3 {
				set start "$curr_year-03-01"
				set end "$curr_year-03-31"
			}
			4 {
				set start "$curr_year-04-01"
				set end "$curr_year-04-30"
			}
			5 {
				set start "$curr_year-05-01"
				set end "$curr_year-05-31"
			}
			6 {
				set start "$curr_year-06-01"
				set end "$curr_year-06-30"
			}
			7 {
				set start "$curr_year-07-01"
				set end "$curr_year-07-31"
			}
			8 {
				set start "$curr_year-08-01"
				set end "$curr_year-08-31"
			}
			9 {
				set start "$curr_year-09-01"
				set end "$curr_year-09-30"
			}
			10 {
				set start "$curr_year-10-01"
				set end "$curr_year-10-31"
			}
			11 {
				set start "$curr_year-11-01"
				set end "$curr_year-11-30"
			}
			12 {
				set start "$curr_year-12-01"
				set end "$curr_year-12-31"
			}
		}
	set query "SELECT * FROM fin_busy WHERE init_date <= '$start' AND end_date >= '$end'"
	set query2 "SELECT SUM(price) FROM (SELECT * FROM fin_busy WHERE init_date <= '$start' AND end_date >= '$end') AS total"
	dict set ::form::sdata start_date $start
	dict set ::form::sdata end_date $end
	} else {
		set query "SELECT * FROM fin_busy"
		set query2 "SELECT SUM(price) FROM (SELECT * FROM fin_busy) AS total"
		}
	set total [lindex [lindex [$::db execute_query $query2] 0] 0]

	set s_data [$::db execute_query $query]
	set columnslist [$::db select_columns_names fin_busy]
	dict set ::form::sdata fin_busy [$::db llist_to_dict $s_data $columnslist]
	dict set ::form::sdata total $total

	load_main report fin_report_busy.xml $::main
}

proc fin_preview_reserv {} {
	set data [dict create]
	set keys [dict keys $::form::fdata]
	set values [dict values $::form::fdata]
	for {set i 0} {$i < [llength $keys]} {incr i} {
		dict set data [lindex $keys $i] [[lindex $values $i] get]	
	}

	set curr_year [dict get $data year]
	set month [dict get $data month]
	if {$month != ""} {
		switch $month {
			1 {
				set start "$curr_year-01-01"
				set end "$curr_year-01-31"
			}
			2 {
				set start "$curr_year-02-01"
				set end "$curr_year-02-28"
			}
			3 {
				set start "$curr_year-03-01"
				set end "$curr_year-03-31"
			}
			4 {
				set start "$curr_year-04-01"
				set end "$curr_year-04-30"
			}
			5 {
				set start "$curr_year-05-01"
				set end "$curr_year-05-31"
			}
			6 {
				set start "$curr_year-06-01"
				set end "$curr_year-06-30"
			}
			7 {
				set start "$curr_year-07-01"
				set end "$curr_year-07-31"
			}
			8 {
				set start "$curr_year-08-01"
				set end "$curr_year-08-31"
			}
			9 {
				set start "$curr_year-09-01"
				set end "$curr_year-09-30"
			}
			10 {
				set start "$curr_year-10-01"
				set end "$curr_year-10-31"
			}
			11 {
				set start "$curr_year-11-01"
				set end "$curr_year-11-30"
			}
			12 {
				set start "$curr_year-12-01"
				set end "$curr_year-12-31"
			}
		}
	set query "SELECT * FROM fin_reserv WHERE init_date <= '$start' AND end_date >= '$end'"
	set query2 "SELECT SUM(price) FROM (SELECT * FROM fin_reserv WHERE init_date <= '$start' AND end_date >= '$end') AS total"
	dict set ::form::sdata start_date $start
	dict set ::form::sdata end_date $end
	} else {
		set query "SELECT * FROM fin_reserv"
		set query2 "SELECT SUM(price) FROM (SELECT * FROM fin_reserv) AS total"
		}
	set total [lindex [lindex [$::db execute_query $query2] 0] 0]

	set s_data [$::db execute_query $query]
	set columnslist [$::db select_columns_names fin_reserv]
	dict set ::form::sdata fin_reserv [$::db llist_to_dict $s_data $columnslist]
	dict set ::form::sdata total $total

	load_main report fin_report_reserv.xml $::main

}